package yesjava;

import java.sql.*;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * JDBC 连接的5个步骤
 * 1. 加载驱动
 * 2. 通过DriverManager 获取数据库连接 connection
 * 3. 通过数据库连接（connection）获取  statement 对象
 * 4. 执行SQL 语句
 * 5. 处理结果
 * 6. 关闭资源
 */

public class JDBCDemo {
    public static void main(String[] args)/* throws Exception*/ {
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
            String sql = " select * from user where id >= ? and id <=? "; //" delete  from user  where id >= ? and id <= ? ";
            String user = "root";
            String password = "root";
            String url = "jdbc:mysql://localhost:3306/mydb?useUnicode=true&useSSL=false&allowPublicKeyRetrieval=true&characterEncoding=utf8&serverTimezone=GMT%2b8";
            String driverClass = "com.mysql.cj.jdbc.Driver";
            Class.forName(driverClass);
            connection = DriverManager.getConnection(url, user, password);
            connection.setAutoCommit(false);//关闭自动提交
            statement = connection.prepareStatement(sql);
            statement.setInt(1, 10);
            statement.setInt(2, 12);
            boolean rsExists = statement.execute();/*select 语句返回true  , 其他返回fasle*/ /* statement.executeQuery(); statement.executeUpdate(); 这两个方法是 execute() 的分支*/
            connection.commit();//执行完后提交
            if (rsExists) {
                resultSet = statement.getResultSet();
                List<User> userList = new ArrayList<>();
                while (resultSet.next()) {
                    int id = resultSet.getInt(1);
                    String name = resultSet.getString(2);
                    String pwd = resultSet.getString("password");
                    Date createTime = resultSet.getTimestamp("create_time");
                    User userEntity = new User(id, name, pwd, createTime);
                    userList.add(userEntity);
                }
                System.out.println(userList);
            } else {
                int count = statement.getUpdateCount();
                System.out.println(count);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (resultSet != null) try {     resultSet.close();   } catch (SQLException e) { e.printStackTrace();}
            if (statement != null) try {       statement.close();      } catch (SQLException e) {   e.printStackTrace();}
            if (connection != null) try {       connection.close();    } catch (SQLException e) {    e.printStackTrace();}
        }
    }
}